﻿using System;
using System.Data;
using System.IO;
using MySql.Data.MySqlClient;
namespace openMap
{
    public class MySqlHelpMan : DataBaseHelpMan 
    {
        public static int i;

        public override void GetConnectString(string ip, string port, string name, string psw, string dataTable)
        {
            Connecting =
                string.Format(
                    "Data Source={0};port={1};User Id={2};Password={3};pooling=true;CharSet=utf8;Allow Zero Datetime=true;Database={4}",
                    ip, port, name, psw, dataTable);
        }

        private static MySqlConnection _mySqlConnection;

        private MySqlConnection MySqlConnection
        {
            get { return _mySqlConnection ?? (_mySqlConnection = new MySqlConnection(Connecting)); }
        }

        public override void OpenConnection()
        {
            if (MySqlConnection.State == ConnectionState.Closed)
                MySqlConnection.Open();
        }

        public override void CloseConnection()
        {
            if (MySqlConnection.State == ConnectionState.Open)
                MySqlConnection.Close();
        }

        public override void ExecuteSql(string sql)
        {
            MySqlCommand cmd = new MySqlCommand(sql, MySqlConnection);
            try
            {
                OpenConnection();

                cmd.ExecuteNonQuery();
            }
            catch (Exception ex)
            {
                Console.WriteLine(ex);
                throw new Exception("获取数据集失败:" + ex.Message);
            }
            finally
            {
                CloseConnection();
            }
        }

        public override DataTable GetDataTable(string sql)
        {

            //PubLog.WriteProcessLog("SQL:" + sql);

            DataTable dataTable = new DataTable();
            try
            {
                OpenConnection();

                //PubLog.WriteProcessLog("打开数据库成功.");

                MySqlCommand cmd = new MySqlCommand(sql, MySqlConnection);

                MySqlDataAdapter adapter = new MySqlDataAdapter(cmd);

                adapter.Fill(dataTable);

                //PubLog.WriteProcessLog("获取数据集成功.");
            }
            catch (Exception ex)
            {
                //PubLog.WriteProcessLog("获取数据集失败:" + ex.Message);
                throw new Exception("获取数据集失败:" + ex.Message);
            }
            finally
            {
                CloseConnection();
            }

            return dataTable;
        }


        public override DataSet GetDataSet(string sql)
        {
            //PubLog.WriteProcessLog("SQL:" + sql);

            DataSet ds = new DataSet();
            try
            {
                OpenConnection();

                //PubLog.WriteProcessLog("打开数据库成功.");

                MySqlCommand cmd = new MySqlCommand(sql, MySqlConnection);

                //cmd.CommandTimeout =PubCommon.ConnectionTimeOut;

                MySqlDataAdapter adapter = new MySqlDataAdapter(cmd);

                adapter.Fill(ds);

                //PubLog.WriteProcessLog("获取数据集成功.");
            }
            catch (Exception ex)
            {
                //PubLog.WriteProcessLog("获取数据集失败:" + ex.Message);
                throw new Exception("获取数据集失败:" + ex.Message);
            }
            finally
            {
                CloseConnection();
            }

            return ds;
        }

        private MySqlDataReader GetDataReader(string sql)
        {
            //PubLog.WriteProcessLog("SQL:" + sql);

            try
            {
                OpenConnection();

                //PubLog.WriteProcessLog("打开数据库成功.");

                MySqlCommand cmd = new MySqlCommand(sql, MySqlConnection);

                //cmd.CommandTimeout = PubCommon.ConnectionTimeOut;

                MySqlDataReader reader = cmd.ExecuteReader();

                if (reader != null)
                {
                    //PubLog.WriteProcessLog("获取数据集成功.");
                    return reader;
                }
            }
            catch (Exception ex)
            {
                //PubLog.WriteProcessLog("获取数据集失败:" + ex.Message);
                throw new Exception("获取数据集失败:" + ex.Message);
            }

            return null;
        }

        public override bool ExecuteFiles(string fileName, string sql)
        {
            try
            {
                using (MySqlDataReader sqlDataReader = GetDataReader(sql))
                {
                    if (sqlDataReader != null)
                    {
                        using (
                            StreamWriter sw = new StreamWriter(fileName, true,
                                System.Text.Encoding.GetEncoding("UTF-8")))
                        {

                            sw.AutoFlush = true;

                            #region 列头

                            int k = sqlDataReader.FieldCount;
                            string title = string.Empty;

                            for (int i = 0; i < k; i++)
                            {
                                string name = sqlDataReader.GetName(i);
                                if (i == 0)
                                    title = name;
                                else
                                    title = title + "," + name;
                            }

                            sw.WriteLine(title);

                            #endregion

                            //PubLog.WriteProcessLog("开始时间：" + DateTime.Now.ToString());
                            //再执行行数据
                            while (sqlDataReader.Read())
                            {
                                //string line = sqlDataReader[j].ToString();

                                string line = string.Empty;

                                for (int i = 0; i < k; i++)
                                {
                                    string value = sqlDataReader[i].ToString();
                                    if (i == 0)
                                        line = value;
                                    else
                                        line = line + "," + value;
                                }

                                sw.WriteLine(line);
                            }

                            //PubLog.WriteProcessLog("计算时间："+DateTime.Now.ToString());

                            sw.Flush();
                            sw.Close();

                            //PubLog.WriteProcessLog("写文件时间：" + DateTime.Now.ToString());

                            sqlDataReader.Close();

                            return true;
                        }
                    }
                }
            }
            catch (Exception exception)
            {
                //PubLog.WriteProcessLog(exception.Message);
            }
            finally
            {
                CloseConnection();
            }

            return false;
        }


    }
}